--/
CREATE PROCEDURE sp_queryTreeBom2

         @bomCode nvarchar(50),--前台传的BOM编码
         @bomVersion nvarchar(50)--前台传的BOM版本
AS
/* Procedure body */
create table #bt_bom_bomTree_view(
        bomlevel int,--阶次
        bomCode nvarchar(50),
        version nvarchar(50),
        pCode nvarchar(50),
        pVersion nvarchar(50),
        materielCode nvarchar(50),
        materielName nvarchar(50),
        bomName nvarchar(50),
        flag int 
)
declare @level int  
set @level =1
declare @isBom int --声明判断输入的BOM编码是否为母件，1是0否

select @isBom =s.countNum from( select count(0) as countNum  from s_bomTop_info2 where bomCode = @bomCode and bomVersion = @bomVersion and status='1') s
print @isBom
IF(@isBom>0)
        begin
                insert into #bt_bom_bomTree_view select @level,s.bomCode+s.bomVersion,s.bomVersion as version,'0' as pCode,'' as 'pVersion',s.materielCode,s.materielName,s.materielCode+','+s.materielName+','+s.bomVersion as bomName,1 as flag from(select bomCode,bomVersion,materielCode,materielName from s_bomTop_info2 where status='1' and bomCode = @bomCode and bomVersion = @bomVersion) s
        end
        
        while exists (select * from #bt_bom_bomTree_view where  #bt_bom_bomTree_view.flag !=0)
       
        begin 
                if exists (select * from  #bt_bom_bomTree_view  where bomlevel = @level and flag!=0)
                 --如果存在下一级，将查着的数据接着添加到临时表中接着查
                        begin
                                set rowcount 1
                                insert into #bt_bom_bomTree_view select  @level+1,s.bomCode+s.version,s.version,s.pCode+s.pVersion,s.pVersion,s.materielCode,s.materielName,s.materielCode+','+s.materielName+','+s.version as bomName,1 as flag from(select bomCode,version,pCode,pVersion,materielCode,materielName from s_bom_info2 where status='1') s 
                                inner join #bt_bom_bomTree_view on #bt_bom_bomTree_view.bomCode = s.pCode+s.pVersion
                                where  #bt_bom_bomTree_view.bomlevel =@level 
                                and s.bomCode+s.version not in (select #bt_bom_bomTree_view.bomCode  from #bt_bom_bomTree_view where bomlevel=@level )and flag!=0 
                               
                                set rowcount 0
                                print 'child node'
                                set @level =@level +1
                                print @level
                        end 
                else if exists(
                        select s.pCode+s.pVersion from s_bom_info2 s  inner join #bt_bom_bomTree_view on #bt_bom_bomTree_view.bomCode = s.pCode+s.pVersion
                        where #bt_bom_bomTree_view.bomlevel=@level-2 and s.status = '1' 
                        and s.bomCode+s.version not in (select #bt_bom_bomTree_view.bomCode from #bt_bom_bomTree_view where bomlevel=@level-1 ))
                         
                        begin
                                set rowcount 1
                                insert into #bt_bom_bomTree_view select @level-1,s.bomCode+s.version,s.version,s.pCode+s.pVersion,s.pVersion,s.materielCode,s.materielName,s.materielCode+','+s.materielName+','+s.version as bomName,1 as flag from ( select bomCode,version,pCode,pVersion,materielCode,materielName from s_bom_info2 WHERE  status = '1') s  
                                inner join #bt_bom_bomTree_view on #bt_bom_bomTree_view.bomCode = s.pCode+s.pVersion
                                where #bt_bom_bomTree_view.bomlevel =@level -2  and s.bomCode+s.version not in (select #bt_bom_bomTree_view.bomCode from #bt_bom_bomTree_view where bomlevel=@level-1 )
                               
                                set @level =@level -1
                                set rowcount 0
                                print 'brother node'
                        end
                else 
                        begin
                                update #bt_bom_bomTree_view set flag =0 where bomlevel =@level-1 and flag !=0  
                                
                                update #bt_bom_bomTree_view set flag =0 where bomlevel =@level-2 and flag !=0  
                                print 'return'
                                
                                set @level =@level -1
                        end 
        end
        select bomCode,pCode,bomName  from #bt_bom_bomTree_view




/
